# from toolbox import PsqlOperting as Psql
from DataTools import Get_163DailyBar
from OtherTools import filetool
from DataTools import datatool
import pandas as pd
import datetime
import os
import re


def tickertoBar(df, pre=60, key="datetime"):
    """
    pre = 周期 = 1-30 60 120 D M
    key = key
    """
    col_L = df.columns.tolist()
    if "price" in col_L:
        col_high = "price"
        col_low = "price"
        col_open = "price"
        col_close = "price"
    else:
        col_high = "high"
        col_low = "low"
        col_open = "open"
        col_close = "close"

    # --------------- #
    # 时间变换模块     #
    if re.search("(\d)", pre):
        df["temp"] = df[key].apply(lambda x: x.split(" ")[-1])
        df["temp2"] = df[key].apply(lambda x: "".join(x.split(" ")[0]))
        pre = int(pre.split("m")[0])
        if pre >= 60:
            if pre == 60:
                df["temp"] = df.temp.apply(lambda x: int("".join(x.split(":"))))
                df["temp"] = df.temp.apply(
                    lambda x: "0"
                    if x <= 103000
                    else ("1" if x <= 113000 else "2" if x <= 140000 else "3")
                )

            elif pre == 120:
                df["temp"] = df.temp.apply(
                    lambda x: "0" if int(x.split(":")[0]) < 13 else "1"
                )
            pre = pre / 60
        elif 0 < pre < 60:
            pre = pre / 60 * 100
            df["temp"] = df.temp.apply(
                lambda x: int(
                    int(x.split(":")[0]) * 60 * 100 + int(x.split(":")[1]) / 60 * 100
                )
            )
            df["temp"] = df.temp.apply(lambda x: str(int(round(float(x)) / pre)))
        df["temp"] = df.temp2 + " " + df.temp
    elif "D" in pre.upper():
        df["temp"] = df[key].apply(lambda x: x.split(" ")[0])
    elif "M" in pre:
        df["temp"] = df[key].apply(lambda x: x.split("-")[0:-1])

    df.sort_values(key, inplace=True)
    # ---------------- #
    # gropuby 到输出   #
    _df = df.groupby("temp")
    Tdf = _df.sum().copy()
    Tdf[key] = _df[key].last()
    if "amount" in Tdf.columns.tolist():
        Tdf["Avg_price"] = _df.amount.sum() / _df.volume.sum()
    else:
        Tdf["Avg_price"] = _df.Amount.sum() / _df.Volume.sum()
    Tdf["high"] = round(_df[col_high].max(), 2)
    Tdf["low"] = round(_df[col_low].min(), 2)
    Tdf["open"] = round(_df[col_open].first(), 2)
    Tdf["close"] = round(_df[col_close].last(), 2)

    l = []
    for num in range(Tdf.shape[0]):
        con = str(int(Tdf.iloc[num]["Avg_price"]))
        op = str(int(Tdf.iloc[num]["open"]))
        if int(con[0]) == 0:
            l.append(num)
    Tdf["Avg"] = Tdf["Avg_price"]
    Tdf["Avg"].iloc[l] = Tdf.iloc[l]["Avg_price"] * 100
    Tdf.iloc[l]["Avg"]
    Tdf.reset_index(inplace=True)

    colL = Tdf.columns.tolist()
    dropL = ["price", "change", "type", "index", "code"]
    for col in dropL:
        if col in colL:
            Tdf.drop(col, inplace=True, axis=1)

    Tdf.drop(["temp", "Avg_price"], inplace=True, axis=1)
    Tdf = Tdf.round(2)
    l = Tdf.columns.tolist()
    l.remove(key)
    l.insert(0, key)
    Tdf = Tdf[l]
    Tdf.sort_values(key, inplace=True)

    return Tdf


def LoadDF(
    code,
    stock_details,
    pre="5",
    startdate="2018-11-10",
    endday="2020-11-10",
    dirDic={
        "Main": "D:/StockDatas/",
        "basic": "basic/",
        "daily": "163_Daily_Bar/",
        "report": "Reports/",
        "temp": "temp/",
        "ticker": "History_ticker/",
        "mins": "History_mins/",
    },
    printLens=60,
):  # SQL转pandas, 对163文件有效
    """
    整合select_stockdatas 和 reformat
    dbName = db_name 数据库名称
    code = 股票代码
    """
    if pre == "D":
        subPath = dirDic["daily"]
    elif re.search("\d", pre):
        subPath = dirDic["mins"]

    FileName = "{}.csv".format(code)
    path = dirDic["Main"] + subPath + FileName

    if "163" not in subPath:
        df = pd.read_csv(path)
        col = df.columns.tolist()
        temp = ["date", "datetime", "time"]
        for c in temp:
            if c in col:
                break
        df = df.rename(columns={c: "date"})
    else:
        df, timer, retrysleep = Get_163DailyBar.RequestData(
            code,
            stock_details,
            counter=0,
            endday=endday,
            totalLens=0,
            timer=0,
            retrysleep=0,
            ParthDic={
                "Main": "D:/StockDatas/",
                "basic": "basic/",
                "daily": "163_Daily_Bar/",
                "report": "Reports/",
                "temp": "temp/",
                "ticker": "History_ticker/",
                "mins": "History_mins/",
            },
            printLens=printLens,
        )

    df.reset_index(inplace=True)
    df.sort_values("date", inplace=True)
    df.drop_duplicates("date", inplace=True)
    df["timestamp"] = pd.to_datetime(df["date"])
    df = df[df["timestamp"] > startdate]

    df = tickertoBar(df, pre=pre, key="date")
    if df.shape[0] > 0:
        lastdate = df.date.iloc[-1]
    else:
        lastdate = datetime.datetime.now().strftime("%Y-%m-%d") + " 09:30:00"

    if re.findall("\d", pre):
        nows = datetime.datetime.now()
        lastdate = datetime.datetime.strptime(lastdate, "%Y-%m-%d %H:%M:%S")
        print(
            "{} >> {}".format(lastdate, nows.strftime("%Y-%m-%d %H:%M:%S")).center(
                printLens, " "
            )
        )

        if lastdate.strftime("%Y-%m-%d") != nows.strftime("%Y-%m-%d"):
            if lastdate < nows:
                tickerPath = dirDic["Main"] + dirDic["ticker"] + FileName
                if filetool.FileCheck(tickerPath):
                    ticker = pd.read_csv(tickerPath)
                    ticker.rename(columns={"datetime": "date"}, inplace=True)
                    ticker["timestamp"] = pd.to_datetime(ticker["date"])
                    ticker = ticker[ticker["timestamp"] > lastdate]
                    ticker = tickertoBar(ticker, pre=pre, key="date")
                    df = pd.concat([df, ticker])

    # print("{} found".format(startdate).center(printLens, " "))
    # print(df.head(1))
    # print(df.tail(1))
    col = df.columns.tolist()
    col.remove("date")
    df = reformat(df, col)
    df = df.sort_values("date")
    df.reset_index(inplace=True)
    df.drop_duplicates("date")

    del col
    return df


def reformat(df, keylist):  # 修改数值,方便后面运算
    """
    由于数据库数值导入可能与需要使用的有所偏差,所以需要重新整合
    df = 导出后的内容
    keylist = 导出后数据
    """

    def convert_currency(var):
        """
        convert the string number to a float
        _ 去除$
        - 去除逗号，
        - 转化为浮点数类型
        """
        new_value = var.replace(",", "").replace("￥", "").replace(",", "")
        return float(new_value)

    for key in keylist:
        a = df.loc[:, key].tolist()[-1]
        if type(a) != int and type(a) != float:
            try:
                df.loc[:, key] = df.loc[:, key].apply(convert_currency)
            except Exception as e:
                print(e)
                if "," in a or "," in a:
                    df.loc[:, key] = df.loc[:, key].apply(lambda x: x.replace(",", ""))

                df.loc[:, key] = df.loc[:, key].apply(lambda x: x.replace("￥", ""))

                df.loc[:, key] = df.loc[:, key].apply(
                    lambda x: float(x.replace(",", ""))
                )
    return df


def getShareDates(
    code,
    stock_details,
    endday="2018-11-10",
    dirDic={
        "Main": "D:/StockDatas/",
        "basic": "basic/",
        "daily": "163_Daily_Bar/",
        "report": "Reports/",
        "temp": "temp/",
        "ticker": "History_ticker/",
        "mins": "History_mins/",
    },
    printLens=60,
):  # 获取总股本等各项信息
    # endday = datatool.getTradedayinfo(
    #     MainPath=dirDic["Main"], subPath=dirDic["basic"], mode="LastOpenDay"
    # )
    StockData, timer, retrysleep = Get_163DailyBar.RequestData(
        code,
        stock_details,
        counter=0,
        endday=endday,
        ParthDic=dirDic,
        printLens=printLens,
    )
    Shares_df = StockData.loc[
        :,
        [
            "date",
            # 'close',
            "TotalVal",
            "TotalShare",
            "CurrencyVal",
            "CurrencyShare",
        ],
    ]

    Shares_df.loc[:, "y"] = Shares_df.loc[:, "date"].apply(
        lambda x: str(x).split("-")[0]
    )
    Shares_df.loc[:, "Q"] = Shares_df.loc[:, "date"].apply(
        lambda x: "-3-31"
        if int(str(x).split("-")[1]) < 4
        else (
            "-6-30"
            if int(str(x).split("-")[1]) < 7
            else ("-9-30" if int(str(x).split("-")[1]) < 10 else "-12-31")
        )
    )
    Shares_df.loc[:, "Qt"] = Shares_df.loc[:, "y"] + Shares_df.loc[:, "Q"]
    GB_Share = (
        Shares_df.loc[
            :,
            [
                "Qt",
                "TotalVal",
                "TotalShare",
                "CurrencyVal",
                "CurrencyShare",
            ],
        ]
        .groupby("Qt")
        .max()
    )
    GB_Share = (
        GB_Share.reset_index().sort_values("Qt").rename({"Qt": "date"}, axis="columns")
    )
    GB_Share.loc[:, "date"] = pd.to_datetime(GB_Share.loc[:, "date"])
    return GB_Share


def reportOutPut(
    Stock_Code,
    MainPath=r"D:/StockDatas/",
    subPath=r"Reports/",
):  # 获取财报个项信息
    """
    获取163报告
    """

    def TenK(df, colName):
        df.loc[:, colName] = df.loc[:, colName].apply(lambda x: float(x) * 10000)
        return df

    def ToDate(df, key):
        df.loc[:, key] = pd.to_datetime(df.loc[:, key])

        df = df.sort_values(key)
        return df

    def TimeSort(df, Month, Month_1, colList_DropdDate):
        df[df.loc[:, "M"] == Month][colList_DropdDate] = (
            df[df.loc[:, "M"] == Month][colList_DropdDate]
            - df[df.loc[:, "M"] == Month_1][colList_DropdDate]
        )
        return df

    def reportLoader(Stock_Code, MainPath, subPath):
        Dir = MainPath + subPath
        path_list = os.listdir(Dir)
        for path in path_list:
            if Stock_Code in path and "3" in path:
                fileName = path

        RP_df = pd.read_csv(Dir + path, encoding="gbk")

        for col in RP_df.columns.tolist():
            if "Unnamed:" in col:
                RP_df = RP_df.drop(col, axis=1)
        # 'tables/reports/1_603701_德宏股份_资产负债表.csv'
        # 'tables/reports/2_002703_浙江世宝_利润表.csv'
        # 'tables/reports/3_002489_浙江永强_现金流量表.csv'
        # print(RP_df.columns)
        RP_df = RP_df.set_index("报告期").T.replace(["--", " --", "-- ", "- -"], 0)
        RP_df = RP_df.reset_index()
        return RP_df

    RP_df = reportLoader(Stock_Code, MainPath, subPath)
    CNtoEn = ["index", "净利润(万元)", "资产总计(万元)", "负债合计(万元)"]
    colList = ["date", "Pure_Profits", "total_gross_asset", "total liabilities"]

    namedic = dict(zip(CNtoEn, colList))
    RP_df.rename(namedic, axis="columns", inplace=True)
    # print(RP_df.columns)
    # print(RP_df.head(5))
    colList_DropdDate = ["Pure_Profits", "total_gross_asset", "total liabilities"]

    RP_df = RP_df.fillna(method="ffill")

    RP_df.loc[:, "date"] = pd.to_datetime(RP_df.loc[:, "date"])

    RP_df.loc[:, "date"] = RP_df.loc[:, "date"].apply(lambda x: str(x).split(" ")[0])
    RP_df.loc[:, "Y"] = RP_df.loc[:, "date"].apply(lambda x: str(x).split("-")[0])
    RP_df.loc[:, "M"] = RP_df.loc[:, "date"].apply(lambda x: str(x).split("-")[1])

    for names in colList_DropdDate:
        RP_df.loc[:, names] = TenK(RP_df, names)

    RP_df_A = RP_df[RP_df.loc[:, "M"] == "12"]

    numberl = list(set(RP_df.loc[:, "M"].tolist()))
    numberl.sort()

    for num in range(len(numberl) - 1):
        n = -num
        RP_df = TimeSort(RP_df, numberl[n], numberl[n - 1], colList_DropdDate)

    RP_df = ToDate(RP_df, "date")
    RP_df_A = ToDate(RP_df_A, "date")
    RP_df = RP_df.fillna(method="ffill")
    return RP_df, RP_df_A


def ReportCal(
    report_Q,
    report_A,
    code,
    stock_details,
    endday="2018-11-10",
    dirDic={
        "Main": "D:/StockDatas/",
        "basic": "basic/",
        "daily": "163_Daily_Bar/",
        "report": "Reports/",
        "temp": "temp/",
        "ticker": "History_ticker/",
        "mins": "History_mins/",
    },
    printLens=60,
):  # 计算PE 每股净资产 , PB 市盈率
    ShareData = getShareDates(
        code,
        stock_details,
        endday=endday,
        dirDic=dirDic,
        printLens=60,
    )

    report_Q = report_Q.loc[
        :, ["date", "Pure_Profits", "total_gross_asset", "total liabilities"]
    ]

    report_A = report_A.loc[
        :, ["date", "Pure_Profits", "total_gross_asset", "total liabilities"]
    ]

    report_Q = pd.merge(report_Q, ShareData, on="date")
    report_A = pd.merge(report_A, ShareData, on="date")

    report_A.loc[:, "PE_last_year_ratio"] = report_A.loc[:, "TotalVal"] / report_A.loc[
        :, "Pure_Profits"
    ].shift(1)

    report_A.loc[:, "P/E_Price_Earnings_Ratio"] = (
        report_Q.loc[:, "TotalVal"] / report_Q.loc[:, "Pure_Profits"]
    )

    report_Q.loc[:, "PB_Price-to-Book"] = (
        report_Q.loc[:, "total_gross_asset"] - report_Q.loc[:, "total liabilities"]
    ) / report_Q.loc[:, "TotalShare"]

    report_Q.loc[:, "PE_TTM"] = (
        report_Q.loc[:, "TotalVal"] / report_Q.loc[:, "Pure_Profits"].rolling(4).sum()
    )
    return report_A, report_Q


def ReportConvent(
    Stock_Code,
    stock_details,
    endday="2018-11-10",
    dirDic={
        "Main": "D:/StockDatas/",
        "basic": "basic/",
        "daily": "163_Daily_Bar/",
        "report": "Reports/",
        "temp": "temp/",
        "ticker": "History_ticker/",
        "mins": "History_mins/",
    },
    printLens=60,
):  # 计算PE 每股净资产 , PB 市盈率
    """
    调用 getShareDates/ reportOutPut/ ReportCal
    """

    report_Q, report_A = reportOutPut(
        Stock_Code, MainPath=dirDic["Main"], subPath=dirDic["report"]
    )
    report_A, report_Q = ReportCal(
        report_Q,
        report_A,
        Stock_Code,
        stock_details,
        endday=endday,
        dirDic=dirDic,
        printLens=60,
    )
    temp = "temp/"
    Dir = dirDic["Main"] + temp
    report_A.to_csv(Dir + "reportA.csv", index=False)
    report_Q.to_csv(Dir + "reportQ.csv", index=False)
    return report_A, report_Q
